--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_SCALE_ORG_LOAF.HQL
--    Functions : 表35：贷款发生额分机构类型分企业规模统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_SCALE_ORG_LOAF PARTITION (DATA_DATE = '#V_DATA_DATE#')
    SELECT
     AREA.AREA_CODE_4                                                            AS FIN_ORG_DIST
    ,FSE.CCY                                                                     AS CCY
    ,ORG.ORG_CODE                                                                AS ORG_TYP             -- 机构类型
    ,ORG.ORG_DSCR                                                                AS ORG_DESC            -- 机构类型描述
    ,FSE.ENTERPRISE_SCALE                                                        AS ENTERPRISE_SCALE    -- 企业规模
    ,CASE WHEN FSE.ENTERPRISE_SCALE='CS01' THEN '大型企业'                       
          WHEN FSE.ENTERPRISE_SCALE='CS02' THEN '中型企业'                       
          WHEN FSE.ENTERPRISE_SCALE='CS03' THEN '小型企业'                       
          WHEN FSE.ENTERPRISE_SCALE='CS04' THEN '微型企业'                       
     END                                                                         AS SCALE_DESC          -- 企业规模描述
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0))/100000000                                 AS OCCUR_AMOUNT        -- 贷款发生额
    ,SUM(COALESCE(FSE.AMOUNT_LM,0))/100000000                                    AS AMOUNT_LM           -- 发生额上期末
    ,SUM(COALESCE(FSE.AMOUNT_YS,0))/100000000                                    AS AMOUNT_YS           -- 当年累计发生额
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0)- COALESCE(FSE.AMOUNT_YC,0))/100000000      AS AMOUNT_GROW         -- 同比增减
    ,SUM(COALESCE(FSE.WSUM_AMOUNT,0))/100000000                                  AS WSUM_AMOUNT         -- 发生额加权值
    ,SUM(COALESCE(FSE.WAMOUNT_LM,0))/100000000                                   AS WAMOUNT_LM          -- 发生额加权值上期末
    ,SUM(COALESCE(FSE.WAMOUNT_YS,0))/100000000                                   AS WAMOUNT_YS          -- 发生额加权值当年累计
    ,SUM(COALESCE(FSE.WAMOUNT_YC,0))/100000000                                   AS WAMOUNT_LC          -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='#V_DATA_DATE#') FSE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON FSE.ORG_TYPE=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,FSE.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR
             ,FSE.ENTERPRISE_SCALE
             ,CASE WHEN FSE.ENTERPRISE_SCALE='CS01' THEN '大型企业'
                   WHEN FSE.ENTERPRISE_SCALE='CS02' THEN '中型企业'
                   WHEN FSE.ENTERPRISE_SCALE='CS03' THEN '小型企业'
                   WHEN FSE.ENTERPRISE_SCALE='CS04' THEN '微型企业'
              END

    UNION ALL

    -- 中小企业
    SELECT
     AREA.AREA_CODE_4                                                            AS FIN_ORG_DIST
    ,FSE.CCY                                                                     AS CCY
    ,ORG.ORG_CODE                                                                AS ORG_TYP            -- 机构类型
    ,ORG.ORG_DSCR                                                                AS ORG_DESC           -- 机构类型描述
    ,'CS234'                                                                     AS ENTERPRISE_SCALE   -- 企业规模
    ,'中小企业'                                                                  AS SCALE_DESC         -- 企业规模描述
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0))/100000000                                 AS OCCUR_AMOUNT        -- 贷款发生额
    ,SUM(COALESCE(FSE.AMOUNT_LM,0))/100000000                                    AS AMOUNT_LM           -- 发生额上期末
    ,SUM(COALESCE(FSE.AMOUNT_YS,0))/100000000                                    AS AMOUNT_YS           -- 当年累计发生额
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0)- COALESCE(FSE.AMOUNT_YC,0))/100000000      AS AMOUNT_GROW         -- 同比增减
    ,SUM(COALESCE(FSE.WSUM_AMOUNT,0))/100000000                                  AS WSUM_AMOUNT         -- 发生额加权值
    ,SUM(COALESCE(FSE.WAMOUNT_LM,0))/100000000                                   AS WAMOUNT_LM          -- 发生额加权值上期末
    ,SUM(COALESCE(FSE.WAMOUNT_YS,0))/100000000                                   AS WAMOUNT_YS          -- 发生额加权值当年累计
    ,SUM(COALESCE(FSE.WAMOUNT_YC,0))/100000000                                   AS WAMOUNT_LC          -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE ENTERPRISE_SCALE IN('CS02','CS03','CS04') AND DATA_DATE='#V_DATA_DATE#') FSE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON FSE.ORG_TYPE=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,FSE.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR

    UNION ALL

    -- 小微企业
    SELECT
     AREA.AREA_CODE_4                                                            AS FIN_ORG_DIST
    ,FSE.CCY                                                                     AS CCY
    ,ORG.ORG_CODE                                                                AS ORG_TYP            -- 机构类型
    ,ORG.ORG_DSCR                                                                AS ORG_DESC           -- 机构类型描述
    ,'CS34'                                                                      AS ENTERPRISE_SCALE   -- 企业规模
    ,'小微企业'                                                                  AS SCALE_DESC         -- 企业规模描述
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0))/100000000                                 AS OCCUR_AMOUNT        -- 贷款发生额
    ,SUM(COALESCE(FSE.AMOUNT_LM,0))/100000000                                    AS AMOUNT_LM           -- 发生额上期末
    ,SUM(COALESCE(FSE.AMOUNT_YS,0))/100000000                                    AS AMOUNT_YS           -- 当年累计发生额
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0)- COALESCE(FSE.AMOUNT_YC,0))/100000000      AS AMOUNT_GROW         -- 同比增减
    ,SUM(COALESCE(FSE.WSUM_AMOUNT,0))/100000000                                  AS WSUM_AMOUNT         -- 发生额加权值
    ,SUM(COALESCE(FSE.WAMOUNT_LM,0))/100000000                                   AS WAMOUNT_LM          -- 发生额加权值上期末
    ,SUM(COALESCE(FSE.WAMOUNT_YS,0))/100000000                                   AS WAMOUNT_YS          -- 发生额加权值当年累计
    ,SUM(COALESCE(FSE.WAMOUNT_YC,0))/100000000                                   AS WAMOUNT_LC          -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE ENTERPRISE_SCALE IN('CS03','CS04') AND DATA_DATE='#V_DATA_DATE#') FSE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON FSE.ORG_TYPE=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,FSE.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR

    UNION ALL

    -- 合计
    SELECT
     AREA.AREA_CODE_4                                                            AS FIN_ORG_DIST
    ,FSE.CCY                                                                     AS CCY
    ,ORG.ORG_CODE                                                                AS ORG_TYP             -- 机构类型
    ,ORG.ORG_DSCR                                                                AS ORG_DESC            -- 机构类型描述
    ,'Z'                                                                         AS ENTERPRISE_SCALE    -- 企业规模
    ,'合计'                                                                      AS SCALE_DESC          -- 企业规模描述
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0))/100000000                                 AS OCCUR_AMOUNT        -- 贷款发生额
    ,SUM(COALESCE(FSE.AMOUNT_LM,0))/100000000                                    AS AMOUNT_LM           -- 发生额上期末
    ,SUM(COALESCE(FSE.AMOUNT_YS,0))/100000000                                    AS AMOUNT_YS           -- 当年累计发生额
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0))/100000000     AS AMOUNT_GROW         -- 同比增减
    ,SUM(COALESCE(FSE.WSUM_AMOUNT,0))/100000000                                  AS WSUM_AMOUNT         -- 发生额加权值
    ,SUM(COALESCE(FSE.WAMOUNT_LM,0))/100000000                                   AS WAMOUNT_LM          -- 发生额加权值上期末
    ,SUM(COALESCE(FSE.WAMOUNT_YS,0))/100000000                                   AS WAMOUNT_YS          -- 发生额加权值当年累计
    ,SUM(COALESCE(FSE.WAMOUNT_YC,0))/100000000                                   AS WAMOUNT_LC          -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04') AND DATA_DATE='#V_DATA_DATE#') FSE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON FSE.ORG_TYPE=ORG.ORG_CODE   
    GROUP BY  AREA.AREA_CODE_4
             ,FSE.CCY;